package clothing.database;


import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.sql.Connection;

import javax.net.ssl.SSLEngineResult.Status;
public class ItemManager {

	
			dbase db;

			public ItemManager() {
				db = new dbase();
			}

			public void insert(String name, String color, String size, 
					double cost, double sale_p, String status) {
				try {
					Class.forName("com.mysql.jdbc.Driver").newInstance();
					Connection conn = DriverManager.getConnection(
							"jdbc:mysql://199.17.162.76/Clothing", "root", "");

					System.out.println("Connected");
					//Statement st = conn.createStatement();
					
//					
//					String query = "insert into employees values('','" + firstname
//							+ "','" + mi + "','" + lastname + "','" + phone + "','"
//							+ address + "','" + type + "','" + username + "','"
//							+ password + "')";
//					System.out.println(query);
					
					PreparedStatement p = conn.prepareStatement("insert into items values('',?,?,?,?,?,?)");
					p.setString(1, name);
					p.setString(2, color);
					p.setString(3, size);
					p.setDouble(4, cost);
					p.setDouble(5, sale_p);
					p.setString(6, status);
					
					
					p.executeUpdate();
					
				} catch (InstantiationException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}

			
			public ArrayList<Item> getList() {
				ArrayList<Item> list = new ArrayList();

				this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "SELECT * from items";

					ResultSet rs = s.executeQuery(query);

					while (rs.next()) {

						int id = rs.getInt("Item_ID");
						String Name = rs.getString("Name");
						String Color = rs.getString("Color");
						String Size = rs.getString("Size");
						double Cost = rs.getDouble("Cost");
						double Sale_Price = rs.getDouble("Sale_Price");
						String Status = rs.getString("Status");

						Item i = new Item(id, Name, Color, Size, Cost, Sale_Price, Status);

						list.add(i);
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				return list;
			}
			
		
			
			public boolean changeStatus(int uid)
			{
				String status ="";
				boolean changed = false;
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();
					
					//Item item = this.getItem(uid);
					String onequery = "Select * from items where Item_ID = '" + uid+"'";
                    ResultSet rs = s.executeQuery(onequery);
                    while(rs.next())
                    {
                    	 status = rs.getString("Status");
                    	 System.out.println(status);
                    }
                    if(status.equalsIgnoreCase("In Stock"))
                    {
                    	String query ="UPDATE items SET Status = 'Sold' WHERE Item_ID = '"+uid+"'";
    					System.out.println(query);
    					s.execute(query);
    					changed = true;
                    }
				
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return changed;
				
			}
			public ArrayList<String> getListNames()
			{
				ArrayList<String> list = new ArrayList();
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Select distinct Name from items";
					
					ResultSet rs = s.executeQuery(query);
					while (rs.next()) 
					{
						String name = rs.getString("Name");
						list.add(name);
					}
					
					//System.out.println(rs.toString());
					}
				catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				return list;
			}
			
			public ArrayList<String> getListColors()
			{
				ArrayList<String> list = new ArrayList();
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Select distinct Color from items";
					
					ResultSet rs = s.executeQuery(query);
					while (rs.next()) 
					{
						String color = rs.getString("Color");
						list.add(color);
					}
					
					//System.out.println(rs.toString());
					}
				catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				return list;
			}
			
			public ArrayList<String> getListSizes()
			{
				ArrayList<String> list = new ArrayList();
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Select distinct Size from items";
					
					ResultSet rs = s.executeQuery(query);
					while (rs.next()) 
					{
						String size = rs.getString("Size");
						list.add(size);
					}
					
					//System.out.println(rs.toString());
					}
				catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				return list;
			}
			
			
			public Item getItem(int id)
			{
				int iid = 0;
				String name = "";
				String color = "";
				String size = "";
				double cost = 0.00;
				double sp = 0.00;
				String status = "";
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Select * from items where Item_ID = '"+id+"'";
					
					
					ResultSet rs = s.executeQuery(query);
					while (rs.next()) {
				    iid = rs.getInt("Item_ID");
					name = rs.getString("Name");
					color = rs.getString("Color");
					size = rs.getString("Size");
					cost = rs.getDouble("Cost");
					sp = rs.getDouble("Sale_Price");
					status = rs.getString("Status");	}				

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				Item i = new Item(iid, name,color, size, cost, sp, status);
				return i;
			}
		    public void updateItem(int iid, String name, String color, 
		    		String size, double cost, double sp)
		    {
		    	this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "UPDATE items SET Name= '"+name+"', Color= '"+color+"' ," +
							"Size= '"+size+"', Cost='"+cost+"', Sale_Price='"+sp+"' WHERE Item_ID = '"+iid+"'";
					s.execute(query);
		    }catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    }


}
